package com.example.singletones;

import java.util.Iterator;
import java.util.Map;
import java.util.Map.Entry;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteQueryBuilder;
import android.util.Log;

public class SQLHelper extends SQLiteOpenHelper {
	SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();
	private static SQLiteDatabase db;
	private static SQLHelper instance;

	public static SQLHelper getInstance() {
		return instance;
	}

	public static SQLHelper getInstance(Context context) {
		if (instance == null) {
			instance = new SQLHelper(context);
		}
		return instance;
	}

	protected SQLHelper(Context context) {
		super(context, "benservice", null, 1);
		db = this.getWritableDatabase();

		// db.execSQL("DROP TABLE MainEvents");
		db.execSQL("CREATE TABLE IF NOT EXISTS MinorEvents (ID INTEGER PRIMARY KEY,Float_X float,Float_Y float,Float_Z float, String_SensorName varchar(60),String_Message text,Time_Event datetime default current_timestamp)");

		db.execSQL("CREATE TABLE IF NOT EXISTS `mainConditions` (id INTEGER PRIMARY KEY,condition TEXT,action TEXT,name TEXT,secondsTaskLength BIGINT,lastPerformed BIGINT)");

		db.execSQL("CREATE TABLE IF NOT EXISTS MainEvents (ID INTEGER PRIMARY KEY,key_1 TEXT, value_1 TEXT, key_2 TEXT, value_2 TEXT, key_3 TEXT, value_3 TEXT,key_4 TEXT, value_4 TEXT,key_5 TEXT, value_5 TEXT,key_6 TEXT, value_6 TEXT, key_7 TEXT, value_7 TEXT, key_8 TEXT, value_8 TEXT, Time_Event datetime default current_timestamp)");
		db.execSQL("create index if not exists `Ikey_1` ON MainEvents (`key_1`)");
		db.execSQL("create index if not exists `Ivalue_1` ON MainEvents (`value_1`)");
		db.execSQL("create index if not exists `Ikey_2` ON MainEvents (`key_2`)");
		db.execSQL("create index if not exists `Ivalue_2` ON MainEvents (`value_2`)");
		db.execSQL("create index if not exists `TimeEvent` ON MainEvents (`Time_Event`)");

		db.execSQL("CREATE TABLE IF NOT EXISTS `Configurations` (name NVARCHAR PRIMARY KEY,data TEXT)");
		db.execSQL("create index if not exists `ConfName` ON Configurations (`name`)");

	}

	public void insertMainEventCat(Map<String, Object> events) {
		String[] args = new String[events.size() * 2];
		Iterator<Entry<String, Object>> it = events.entrySet().iterator();
		int j = 0;
		while (it.hasNext()) {
			Entry<String, Object> current = it.next();
			args[j] = current.getKey();
			args[j + 1] = current.getValue().toString();
			j += 2;
		}
		insertMainEventCat(args);
	}

	public void insertMainEventCat(String... args) {
		StringBuilder cmd = new StringBuilder();
		StringBuilder values = new StringBuilder();
		String[] values1 = new String[args.length];
		cmd.append("INSERT INTO `MainEvents` (");
		int j = 1;
		for (int i = 0; i < args.length; i += 2) {
			if (i > 0) {
				cmd.append(",");
				values.append(",");
			}
			cmd.append("`key_" + j + "`");
			cmd.append(",`value_" + j + "`");
			values1[i] = args[i];
			values1[i + 1] = args[i + 1];
			values.append("?,?");
			j++;
		}
		cmd.append(")");
		cmd.append(" VALUES (");
		cmd.append(values);
		cmd.append(")");
		db = this.getWritableDatabase();
		Log.d("message", cmd.toString() + " - " + commaDelimitered(values1));
		db.execSQL(cmd.toString(), values1);
	}

	public String commaDelimitered(String[] args) {
		StringBuilder str = new StringBuilder();
		for (String i : args) {
			str.append(i);
			str.append(",");
		}
		str.setLength(str.length() - 1);
		return str.toString();
	}

	public void insertMainEvent2Cat(String sensorName, String category_1,
			String category_2, String... args) {
		StringBuilder cmd = new StringBuilder();
		StringBuilder values = new StringBuilder();
		String[] values1 = new String[args.length + 3];
		cmd.append("INSERT INTO `MainEvents` (`String_SensorName`, String_Category_1`, `String_Category_2`");
		int j = 1;
		values1[0] = sensorName;
		values1[1] = category_1;
		values1[2] = category_2;
		for (int i = 0; i < args.length; i += 2) {
			cmd.append(",");
			if (i > 0)
				values.append(",");
			cmd.append("`key_" + j + "`");
			cmd.append(",`value_" + j + "`");
			values1[i + 3] = "'" + args[i] + "'";
			values1[i + 4] = "'" + args[i + 1] + "'";
			values.append("?,?");
			j++;
		}
		cmd.append(")");
		cmd.append(" VALUES (");
		values.append(",?,?,?");
		cmd.append(values);
		cmd.append(")");
		db = this.getWritableDatabase();
		Log.d("message", cmd.toString() + " - " + commaDelimitered(values1));
		db.execSQL(cmd.toString(), values1);
	}

	public void insertMainEvent(String sensorName, String category,
			String message) {
		insertMainEvent(sensorName, category, "", message);
	}

	public void insertMainEvent(String sensorName, String category,
			String category2, String message) {
		String[] SQLData = new String[4];
		SQLData[0] = message;
		SQLData[1] = category;
		SQLData[2] = category2;
		SQLData[3] = sensorName;
		db = this.getWritableDatabase();
		Log.d("message",
				"INSERT INTO `MainEvents` (`String_Message`,`String_Category_1`,`String_Category_2`,`String_SensorName`) VALUES ('"
						+ SQLData[0]
						+ "','"
						+ SQLData[1]
						+ "','"
						+ SQLData[2]
						+ "','" + SQLData[3] + "')");
		db.execSQL(
				"INSERT INTO `MainEvents` (`String_Message`,`String_Category_1`,`String_Category_2`,`String_SensorName`) VALUES (?,?,?,?)",
				SQLData);
	}

	public void insertConfigData(String name, String data) {
		db = this.getWritableDatabase();
		String[] toSQL = new String[2];
		toSQL[0] = name;
		toSQL[1] = data;
		db.execSQL("replace into `Configurations` (name,data) values (?,?)",
				toSQL);
	}

	public String selectConfigData(String keyName, String defaultValue) {
		String[] args = new String[1];
		args[0] = defaultValue;
		Cursor cursor = db.rawQuery(
				"select data from `Configurations` where name=? limit 1", args);
		while (cursor.moveToNext()) {
			return cursor.getString(0);
		}
		return defaultValue;
	}

	@Override
	public void onCreate(SQLiteDatabase arg0) {

	}

	@Override
	public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
		// TODO Auto-generated method stub

	}

}
